﻿using System;
using System.IO;
using System.Linq;
using System.Collections.Generic;
using Supermarket.Data;
using Newtonsoft.Json;
using MongoDB.Driver;
using MongoDB.Bson;
using MongoDB.Bson.Serialization;
using SupermarketModel;
using System.Xml.Linq;
using System.Globalization;

namespace Supermarket.Client
{
    class DataExporter
    {
        public static void ExportToPdf()
        {
            PDFReporter.GenerateReport(Directory.GetCurrentDirectory() + "../../../../PDFSalesReport.pdf");
        }

        public static void ExportToSqLite()
        {
            ProductReports.FillSqLite();
        }

        public static void ExportToExcel()
        {
            ProductReports.FillExcelReport();
        }

        public static void ExportToJson()
        {
            var sqlDb = new SupermarketContext();

            using (sqlDb)
            {
                string sqlQuery = "SELECT p.ID AS productId, p.Name AS productName, v.Name AS vendorName, SUM(sr.Quantity) AS totalQuantitySold, SUM(sr.Sum) AS totalIncomes FROM Vendors v INNER JOIN Products p ON v.ID = p.VendorID INNER JOIN SalesReports sr ON p.ID = sr.ProductId GROUP BY p.ID, p.Name, v.Name";
                var totalSales = sqlDb.Database.SqlQuery<JsonObject>(sqlQuery).ToList();
                foreach (var sale in totalSales)
                {
                    string json = JsonConvert.SerializeObject(sale);
                    if (DirectoryExist(@"../../../JSON/"))
                    {
                        File.WriteAllText(@"../../../JSON/" + sale.productId + ".json", json);
                    }
                    MongoDbProvider.db.SaveData(BsonSerializer.Deserialize<BsonDocument>(json));
                }
            }
        }

        public static void ExportToXMLReport()
        {
            using(SupermarketContext database = new SupermarketContext())
	        {
                IEnumerable<XMLSalesReport> reports = database.Database.SqlQuery<XMLSalesReport>(
                    @"SELECT v.Name, sr.ReportDate, SUM(sr.Sum) AS TotalSum FROM SalesReports sr
                    JOIN Products p
                    ON sr.ProductId = p.ID
                    JOIN Vendors v
                    ON v.ID = p.VendorID
                    GROUP BY v.Name, sr.ReportDate
                    ORDER BY v.Name, sr.ReportDate"
                );

                
                XElement root = new XElement("sales");
                XDocument documnet = new XDocument(new XDeclaration("1.0", "utf-8", "yes"), root);
                string prevVendorName = null;
                XElement currentSale = null;
                foreach (var report in reports)
                {
                    if (report.Name != prevVendorName)
                    {
                        currentSale = new XElement("sale", new XAttribute("vendor", report.Name));
                        root.Add(currentSale);
                        prevVendorName = report.Name;
                    }

                    string date = report.ReportDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture);

                    XElement summary = new XElement("summary",
                        new XAttribute("date", date), 
                        new XAttribute("total-sum", report.TotalSum));
                    currentSale.Add(summary);
                }

                documnet.Save("../../../XMLSalesReport.xml");

	        }
        }

        public static bool DirectoryExist(string directoryPatch)
        {
            DirectoryInfo objDirectory = new DirectoryInfo(directoryPatch);
            if (objDirectory.Exists)
            {
                return true;
            }
            else
            {
                try
                {
                    Directory.CreateDirectory(directoryPatch);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
    }
}